In [10]:
import sys
sys.path.append('../utils/')

import pandas.io.data as web
import seaborn as sns
from sqlalchemy import create_engine
import datetime
import pandas as pd
from pygments import highlight
from pygments.lexers.sql import SqlLexer
from pygments.formatters import HtmlFormatter, LatexFormatter
from IPython import display
import functools as ft
import matplotlib.pyplot as plt
import scipy as sp
import scipy.interpolate
from __future__ import division
import numpy as np
import matplotlib.dates as mdates
from scipy.optimize import minimize
from matplotlib.finance import candlestick2_ohlc
from datetime import date
%matplotlib inline
import numpy as np
import matplotlib.pyplot as plt
from mpl_toolkits.mplot3d import Axes3D
from matplotlib import cm
from HTMLParser import HTMLParser
import requests
import re
from dateutil import parser
from progressbar import ProgressBar
from trading_days import TradingDay
from itertools import chain
from scipy.optimize import curve_fit
from datetime import date, timedelta

CONNECTION_STRING = 'mssql+pymssql://IVYuser:resuyvi@vita.ieor.columbia.edu'

# Gets the database connection
def get_connection():
    engine = create_engine(CONNECTION_STRING)
    return engine.connect()

# Query database and return results in dataframe
def query_dataframe(query, connection=None):
    if connection is None:
        connection = get_connection()
    return pd.read_sql(query, connection)

# Query database using external file and return results in dataframe
def query_dataframe_f(filename, connection=None):
    if connection is None:
        connection = get_connection()
    with open(filename, 'r') as handle:
        return pd.read_sql(handle.read(), connection)

# Print sql query and query results
def print_and_query(filename, connection=None, use_latex=False):
    if connection is None:
        connection = get_connection()
    with open(filename, 'r') as handle:
        sql = handle.read()
        if use_latex:
            display_obj = display.Latex(highlight(
                sql, SqlLexer(), LatexFormatter()))
        else:
            formatter = HtmlFormatter()
            display_obj = display.HTML(
                '<style type="text/css">{}</style>{}'
                .format(
                formatter.get_style_defs('.highlight'),
                highlight(sql, SqlLexer(), formatter)))
        display.display(
            display_obj,
            pd.read_sql(sql, connection)
        ) 

def data_frame_to_sql(df, table_name):
    engine = create_engine(CONNECTION_STRING)
    df.to_sql(table_name, engine, if_exists='replace',index=False)
In [11]:
def trading_window(date,pre,post):
    '''
        Selecting a (-pre,+post) interval of trading days aroun date
    '''
    return pd.Series([(date + TradingDay(i))
            for i in range(pre,post+1)])

def bus_days_between(start, end):
    if start.date() == end.date():
        return 0
    elif start < end:
        return len(pd.date_range(start=start.date(),
                                 end=end.date(),
                                 closed='left',
                                 freq=TradingDay(1)))
    else:
        return -len(pd.date_range(start=end.date(),
                                  end=start.date(),
                                  closed='left',
                                  freq=TradingDay(1)))
In [12]:
'''
    American Option Pricer with dividends - Function call
    ABM(ft.partial(VP,K=Strike,CallPut='OptType'),StockPrice,TimeToMaturity(1/360), 
               Interest Rate, Volatility, TreeSteps, Dividends))
    Dividends are given in the format np.array([[time, dividend],....,])
'''

def BPTree(n, S, u, d, ex_div):
    # Creating a binomial tree with dividends adjustment
    r = [np.array([S])]
    for i in range(n):
        if (i in ex_div[:,0]):
            adj = ex_div[ex_div[:,0]==i][0,1]
            r.append(np.concatenate((r[-1][:1]*u-adj, r[-1]*d-adj)))
        else: r.append(np.concatenate((r[-1][:1]*u, r[-1]*d))) 
    return r

def GBM(R, P, S, T, r, v, n, ex_div):
    # Function returns the American option price
    t = float(T)/n
    T_range = np.arange(0,T+t,t)
    # Selecting dividends before option's maturity date
    curr_div = ex_div[ex_div[:,0]<=T]
    div_idx = []
    # Indexing the dividends ex date in the binomial tree
    for i in range (len(curr_div[:,0])):
        T_diff = T_range[1:]-curr_div[i,0]
        T_diff[T_diff<0]=100000
        div_idx.append(np.argmin(T_diff))
    curr_div[:,0] = np.array(div_idx)
    # Defining up/down binomial step coefficients 
    u = np.exp(v * np.sqrt(t))
    d = 1./u
    p = (np.exp(r * t) - d)/(u - d)
    # Creating the binomial tree
    ptree = BPTree(n, S, u, d, curr_div)[::-1]
    # Defining a function for discounting and P-measure in the tree
    R_ = ft.partial(R, np.exp(-r*t), p)
    # Discounting through the tree with american exercise option
    return ft.reduce(R_, map(P, ptree))[0]

def American(D, p, a, b): 
    # Selecting maximum between continuation and intrinsic option value
    return np.maximum(b, D*(a[:-1]*p + a[1:]*(1-p)))

def VP(S, K, CallPut): 
    # Intrinsic value
    if (CallPut=='C'): return np.maximum(S-K, 0)
    else: return np.maximum(K-S, 0)
    
ABM = ft.partial(GBM, American)
In [13]:
def synthetic_ATM(ticker,earning_date,expiration,opt_type,method,day_range,steps):
    '''
    SYNTHETIC OPTIONS
    Function returns a table with the following features for synthetic options 
    in the day_range around the earning date for a selected expiration of a 
    selected ticker between Date_Start and Date_End:
        - Date.
        - Stock Price.
        - CallPut option type.
        - Expiration.
        - Strike.
        - Interpolated MBBO.
        - Implied volatility obtained by inverting Black-Scholes equation.
        - Method is for chosing either DeltaStraddle=0 or Stike=StockPrice ATM
    '''
    # Obtaining the date range around the earning date
    earning_date = datetime.datetime.date(earning_date)
    date_range = trading_window(earning_date,
                                day_range[0],day_range[1]).astype(datetime.date)
    
    # Get option ATM serieses 
    date_range = date_range[date_range<expiration]    
    sql_raw = open('ATM.sql', 'r').read()
    sql_format = sql_raw.format(
        ticker = ticker, 
        date_start = str(date_range.values[0])[:10], # yyyy-MM-dd
        date_end = str(date_range.values[-1])[:10], # yyyy-MM-dd
        date_expiration = str(expiration)[:10],
    )
    data = query_dataframe(sql_format) 
    # Synthetic options method selection
    data = data[data.ATMethod==method]   
    # Obtaining Straddle Delta=0 synthetic strikes 
    delta_strikes = {}
    if method=='D':
        for date, df in data.groupby(['Date']):
            delta_straddle = {}
            for K,df_K in df.groupby('Strike'):
                delta_straddle[K] = df_K.Delta.values[0]+df_K.Delta.values[1]
            delta_straddle = pd.Series(delta_straddle,index=delta_straddle.keys())
            spline = sp.interpolate.interp1d(delta_straddle.values,delta_straddle.index)
            delta_strikes[date]=float(spline(0))
        
    # Obtaining the dividends dates and dollar amount
    query = ''' SELECT ExDate, Amount
                    FROM XFDATA.dbo.DISTRIBUTION dist 
                    INNER JOIN XFDATA.dbo.SECURITY sec ON dist.SecurityID=sec.SecurityID
                    WHERE Ticker='%s' AND (DistributionType='%s' OR DistributionType='1')
                    AND ExDate BETWEEN '%s' AND '%s'  ''' % (ticker,
                                    '%',str(date_range.values[0])[:10],expiration)
    dividends = query_dataframe(query)
  
    # Creating an empty dataframe for output data
    data_out = pd.DataFrame(columns=['Date','StockPrice','CallPut','Expiration',
                                     'TimeToMaturity','Strike','MBBO','IV','ATM Method'])
    
    # Construct a synthetic option on each date in the considered range
    for date, df in data.groupby(['Date','CallPut']):
        # Variables     
        S = df.ClosePrice.values[0]
        # Selecting synthetic strike price based on the method
        if method=='S': 
            X = df.ClosePrice.values[0]
        else: 
            X = delta_strikes[date[0]]
        CP = df.CallPut.values[0]
        T = (bus_days_between(date[0],expiration))*1./252
        r = df.ZeroRate.values[0]*0.01
        # Setting an intital value for the IV in the optimization (avoiding -99)
        if (df.ImpliedVolatility[df.ImpliedVolatility>0].mean()>0): 
            # Set the initial value of the IV to the mean of the 4 options considered
            IV_0=df.ImpliedVolatility[df.ImpliedVolatility>0].mean()
        else:
            # Set the initial value to the mean IV of the previous day
            IV_0=data.ImpliedVolatility[data.Date==(date[0]-datetime.timedelta(1))].mean()
        # Time to ex dividend date
        time_to_ExDate = np.array([bus_days_between(date[0],t)*1./252 for t in dividends.ExDate])
        # Dividend table with maturity of Ex Div dates
        div_to_expiration = np.array([time_to_ExDate,dividends.Amount]).T 
        # Linear option price interpolation of the closest option data
        if X in df.Strike.values:
            # Check if option exists with desired characteristics
            MBBO_synthetic = float(df.MBBO.values[0])
        else:
            spline = sp.interpolate.interp1d(df.Strike.values,df.MBBO.values)
            MBBO_synthetic = float(spline(X))
        # Defining the objective function for optimization
        def f(x):
            return (ABM(ft.partial(VP,K=X,CallPut=CP),S, T, r, x, steps, 
                    div_to_expiration[div_to_expiration[:,0]>=0])-MBBO_synthetic)**2
        # Defining mimization constraints
        cons = ({'type': 'ineq',
                 'fun' : lambda x: np.array(x),
                 'jac': lambda x: np.array([1.0])})
        # Optimizing
        res = minimize(f,IV_0,constraints=cons)
        if method=='S': exp_method='ATM Strike'
        else: exp_method='ATM Delta'
        # Append data
        s = pd.Series([date[0],S,CP,expiration,T,X,MBBO_synthetic,float(res.x),exp_method],
                  index=['Date','StockPrice','CallPut',
                                 'Expiration','TimeToMaturity','Strike','MBBO','IV','ATM Method'])
        data_out = data_out.append(s,ignore_index=True)        
    return data_out[data_out.CallPut==opt_type]
    

Exercise 1

Pick three optionable stocks.

a) Using the Internet, make a table of announced earnings dates for the two-year period 6/1/2011-6/1/2013.

In [14]:
start_date = '2011-06-01'
end_date = '2013-06-01'
In [15]:
class EarningsParser(HTMLParser):
    '''
        Function parsing date data from HTML file
    '''
    store_dates = False
    earnings_offset = None
    dates = []

    def __init__(self, *args, **kwargs):
        #super().__init__(*args, **kwargs)
        HTMLParser.__init__(self)
        self.dates = []

    def handle_starttag(self, tag, attrs):
        if tag == 'table':
            self.store_dates = True

    def handle_data(self, data):
        if self.store_dates:
            match = re.match(r'\d+/\d+/\d+', data)
            if match:
                self.dates.append(match.group(0))

        # If a company reports before the bell, record the earnings date
        # being at midnight the day before. Ex: WMT reports 5/19/2016,
        # but we want the reference point to be the closing price on 5/18/2016
        if 'After Close' in data:
            self.earnings_offset = timedelta(days=0)
        elif 'Before Open' in data:
            self.earnings_offset = timedelta(days=-1)

    def handle_endtag(self, tag):
        if tag == 'table':
            self.store_dates = False
In [16]:
def earnings_releases(ticker,start_date,end_date):
    '''
        Function returns earning dates for the selected date range and ticker
        from streetinsider.com
    '''
    
    user_agent = 'Mozilla/5.0 (Windows NT 10.0; WOW64; rv:46.0) '\
        'Gecko/20100101 Firefox/46.0'
    headers = {'user-agent': user_agent}
    base_url = 'http://www.streetinsider.com/ec_earnings.php?q={}'\
        .format(ticker)
    e = EarningsParser()
    s = requests.Session()
    a = requests.adapters.HTTPAdapter(max_retries=0)
    s.mount('http://', a)
    e.feed(str(s.get(base_url, headers=headers).content))

    if e.earnings_offset is not None:
        dates = map(lambda x: parser.parse(x) + e.earnings_offset, e.dates)
        past = filter(lambda x: x < datetime.datetime.now(), dates)
        res = pd.DataFrame(list(map(lambda d: d.isoformat(), past)),
                           columns=['EarningDate']).astype(np.datetime64)
        res = res[(res<np.datetime64(end_date))\
                                 & (res>np.datetime64(start_date))]
        res.dropna(inplace=True)
        # Checking for the right number of earning dates (4/year)
        earn_expected_n = int((parser.parse(end_date)-parser.parse(start_date)).days/365*4)
        if len(res)<earn_expected_n: print('Check for possible missing earning dates')
        return res
In [17]:
earnings_releases('AAPL',start_date,end_date)
Out[17]:
EarningDate
13 2013-04-23 04:00:00
14 2013-01-23 05:00:00
15 2012-10-25 04:00:00
16 2012-07-24 04:00:00
17 2012-04-24 04:00:00
18 2012-01-24 05:00:00
19 2011-10-18 04:00:00
20 2011-07-19 04:00:00
In [18]:
earnings_releases('CELG',start_date,end_date)
Out[18]:
EarningDate
13 2013-04-24 04:00:00
14 2013-01-23 05:00:00
15 2012-10-24 04:00:00
16 2012-07-25 04:00:00
17 2012-04-25 04:00:00
18 2012-01-25 05:00:00
19 2011-10-26 04:00:00
20 2011-07-27 04:00:00
In [19]:
earnings_releases('GOOG',start_date,end_date)
Out[19]:
EarningDate
13 2013-04-18 04:00:00
14 2013-01-22 05:00:00
15 2012-10-18 04:00:00
16 2012-07-19 04:00:00
17 2012-04-12 04:00:00
18 2012-01-19 05:00:00
19 2011-10-13 04:00:00
20 2011-07-14 04:00:00

b) For each stock, identify the option series that will be: (A) the front month at earnings, (B) the next available series, (C) the first January leap (this will be no sooner than the fifth available option month).

In [20]:
def earnings_expirations(ticker,start_date, end_date):
    '''
        The function obtains earning date, front month, second month and January leap
        expiration for the selected ticker and date range. Expirations if 
        recorded as saturdays on IVY will converted to previous trading day expirations.
        (ATM query within synthetic_ATM() function works with these adjusted dates for
        options expirations)
    '''
    df_out = pd.DataFrame(columns=['EarningDate','First','Second','JanLeap'])
    # Obtaining earning dates from the web
    df_out['EarningDate'] = earnings_releases(ticker,start_date,end_date).EarningDate
    # Initializing the dataframe
    df_out[['First','Second','JanLeap']] = datetime.datetime.now()
    # Creating a SQL table Earnings
    data_frame_to_sql(df_out,'Earnings')
    sql_raw = open('Exp.sql', 'r').read()
    sql_format = sql_raw.format(
                    ticker = ticker,
                    date_start = start_date,
                    date_end = end_date)
    res = query_dataframe(sql_format)
    df_out['First'] = res.First.where(res.First.dt.dayofweek!=5, 
                                      res.First[res.First.dt.dayofweek==5]-TradingDay(1)).values
    df_out['Second'] = res.Second.where(res.Second.dt.dayofweek!=5,
                                      res.Second[res.Second.dt.dayofweek==5]-TradingDay(1)).values
    df_out['JanLeap'] = res.JanLeap.where(res.JanLeap.dt.dayofweek!=5,
                                      res.JanLeap[res.JanLeap.dt.dayofweek==5]-TradingDay(1)).values
    return df_out.reset_index(drop=True)
In [21]:
earnings_expirations('AAPL',start_date,end_date)
Out[21]:
EarningDate First Second JanLeap
0 2013-04-23 04:00:00 2013-05-17 2013-06-21 2014-01-17
1 2013-01-23 05:00:00 2013-02-15 2013-03-15 2014-01-17
2 2012-10-25 04:00:00 2012-11-16 2012-12-21 2014-01-17
3 2012-07-24 04:00:00 2012-08-17 2012-09-21 2013-01-18
4 2012-04-24 04:00:00 2012-05-18 2012-06-15 2013-01-18
5 2012-01-24 05:00:00 2012-02-17 2012-03-16 2013-01-18
6 2011-10-18 04:00:00 2011-10-21 2011-11-18 2013-01-18
7 2011-07-19 04:00:00 2011-08-19 2011-09-16 2012-01-20
In [22]:
earnings_expirations('CELG',start_date,end_date)
Out[22]:
EarningDate First Second JanLeap
0 2013-04-24 04:00:00 2013-05-17 2013-06-21 2014-01-17
1 2013-01-23 05:00:00 2013-02-15 2013-03-15 2014-01-17
2 2012-10-24 04:00:00 2012-11-16 2012-12-21 2014-01-17
3 2012-07-25 04:00:00 2012-08-17 2012-09-21 2013-01-18
4 2012-04-25 04:00:00 2012-05-18 2012-06-15 2013-01-18
5 2012-01-25 05:00:00 2012-02-17 2012-03-16 2013-01-18
6 2011-10-26 04:00:00 2011-11-18 2011-12-16 2013-01-18
7 2011-07-27 04:00:00 2011-08-19 2011-09-16 2012-01-20
In [23]:
earnings_expirations('GOOG',start_date,end_date)
Out[23]:
EarningDate First Second JanLeap
0 2013-04-18 04:00:00 2013-04-19 2013-05-17 2014-01-17
1 2013-01-22 05:00:00 2013-02-15 2013-03-15 2014-01-17
2 2012-10-18 04:00:00 2012-10-19 2012-11-16 2014-01-17
3 2012-07-19 04:00:00 2012-07-20 2012-08-17 2013-01-18
4 2012-04-12 04:00:00 2012-04-20 2012-05-18 2013-01-18
5 2012-01-19 05:00:00 2012-01-20 2012-02-17 2013-01-18
6 2011-10-13 04:00:00 2011-10-21 2011-11-18 2013-01-18
7 2011-07-14 04:00:00 2011-07-15 2011-08-19 2012-01-20

Exercise

We wish to follow the implied volatilities of the ATM straddles of various series beginning approximately three weeks before earnings and proceeding to one week after earnings.

NOTE: for this problem, we do not use the volatility surface tables in IVY to track the 50- delta vols. Why, you ask? The volatility surface table uses time averaging and therefore minimizes drops across earnings events.

a)

  • Choose one of the stocks from 1a.
  • For each earnings event, create three synthetic ATM options, one for each of the three series identified in 1b plot their implied vols for the 4 week period
  • The result will be 3 series x 4 earnings = 12 curves.

NOTE: The front month series may truncate with expiry but the other two will continue past expiration.

In [24]:
start_date = '2011-06-01'
end_date = '2012-06-01'
# Selecting a Stock
Ticker = 'AAPL'
# Earnings and expirations table
earnings_expirations(Ticker,start_date,end_date)
Out[24]:
EarningDate First Second JanLeap
0 2012-04-24 04:00:00 2012-05-18 2012-06-15 2013-01-18
1 2012-01-24 05:00:00 2012-02-17 2012-03-16 2013-01-18
2 2011-10-18 04:00:00 2011-10-21 2011-11-18 2013-01-18
3 2011-07-19 04:00:00 2011-08-19 2011-09-16 2012-01-20
In [25]:
def earnings_option_series(ticker,start_date,end_date,opt_type):
    res = {}
    # Earnings and expirations table
    df_earnings_exp = earnings_expirations(ticker,start_date,end_date)
    for t in range(df_earnings_exp.shape[0]):
        first_exp = df_earnings_exp.First.loc[t]
        second_exp = df_earnings_exp.Second.loc[t]
        janleap_exp = df_earnings_exp.JanLeap.loc[t] 
        earning_date = df_earnings_exp.EarningDate.loc[t]
        first = synthetic_ATM(ticker,earning_date,first_exp,opt_type,'S',[-15,5],100)
        second = synthetic_ATM(ticker,earning_date,second_exp,opt_type,'S',[-15,5],100)
        janleap = synthetic_ATM(ticker,earning_date,janleap_exp,opt_type,'D',[-15,5],100)
        
        if opt_type=='C': callput='Call'
        else: callput='Put'
            
        #fig = plt.figure(figsize=(15,6))
        fig, ax1 = plt.subplots(figsize=(15,6))
        ax2 = ax1.twinx()

        # Plotting implied volatilities
        ax1.plot(first.Date.values, first.IV,'-bo',
                 label=(callput+' IV First ' +str(first_exp)[:10]))
        ax1.plot(second.Date.values, second.IV,'-go',
                 label=callput+' IV Second ' +str(second_exp)[:10])
        ax1.plot(janleap.Date.values, janleap.IV,'-ro', 
                 label=callput+' IV JanLeap '+str(janleap_exp)[:10])
        
        # Plotting the stock price
        ax2.plot(janleap.Date.values, janleap.StockPrice, '--k',
                 label=ticker + ' stock price')

        ax1.set_ylabel('Implied Volatility')
        ax2.set_ylabel('Stock Price')
        ax1.legend(loc=2)
        ax2.legend(loc=0)
        plt.title(ticker +' Earning Date '+str(earning_date)[:10])
        plt.show()
        
        #fig = plt.figure(figsize=(15,6))
        fig, ax1 = plt.subplots(figsize=(15,6))
        ax2 = ax1.twinx()

        # Plotting implied volatilities
        ax1.plot(first.Date.values, first.IV,'-bo',
                 label=(callput+' IV First ' +str(first_exp)[:10]))
        ax1.plot(second.Date.values, second.IV,'-go',
                 label=callput+' IV Second ' +str(second_exp)[:10])
        ax1.plot(janleap.Date.values, janleap.IV,'-ro', 
                 label=callput+' IV JanLeap '+str(janleap_exp)[:10])

        # Plotting the stock price
        ax2.plot(first.Date.values, first.MBBO.values,
                 '--k', label=ticker + ' ATM front month synthetic MBBO')

        ax1.set_ylabel('Implied Volatility')
        ax2.set_ylabel('MBBO')
        ax1.legend(loc=2)
        ax2.legend(loc=0)
        plt.title(ticker +' Earning Date '+str(earning_date)[:10])
        plt.show()
        
        # Saving data
        res[earning_date]={'First':first, 'Second':second,'JanLeap':janleap}
        
        # restricting data before earning date
        first = first[first.Date<earning_date]
        second = second[second.Date<earning_date]
        janleap = janleap[janleap.Date<earning_date]
        idx_first = np.array(range(first.shape[0]))
        idx_second = np.array(range(second.shape[0]))
        idx_janleap = np.array(range(janleap.shape[0]))
        
        # Quadratic fit
        fig = plt.figure(figsize=(15,6))
        fit_first = np.poly1d(np.polyfit(idx_first,first.IV.values,deg=2))
        fit_second = np.poly1d(np.polyfit(idx_second,second.IV.values,deg=2)) 
        fit_janleap = np.poly1d(np.polyfit(idx_janleap,janleap.IV.values,deg=2))
        
        plt.plot(idx_first, first.IV.values,'-bo',
                 label=(callput+' IV First ' +str(first_exp)[:10]))
        plt.plot(idx_first, fit_first(idx_first),'--b')
        plt.plot(idx_second, second.IV.values,'-go',
                 label=(callput+' IV Second ' +str(second_exp)[:10]))
        plt.plot(idx_second, fit_second(idx_second),'--g')
        plt.plot(idx_janleap, janleap.IV.values,'-ro',
                 label=(callput+' IV JanLeap ' +str(janleap_exp)[:10]))
        plt.plot(idx_janleap, fit_janleap(idx_janleap),'--r')
        plt.grid(True)
        plt.legend(loc=0)
        plt.ylabel('Implied Volatility')
        plt.title(ticker + ' Earning Date '+str(earning_date)[:10]+' quadratic fitting')
        plt.show()
        
        # Exponential fit
        def func(x, a, b, c):
            return a * np.exp(-b * x) + c

        popt_first, pcov_first = curve_fit(func,idx_first,first.IV.values,maxfev=10000)
        popt_second, pcov_second = curve_fit(func,idx_second,second.IV.values, maxfev=10000)
        popt_janleap, pcov_janleap = curve_fit(func,idx_janleap,janleap.IV.values, maxfev=10000)    
        
        fig = plt.figure(figsize=(15,6))
        plt.plot(idx_first, first.IV.values,'-bo',
                 label=(callput+' IV First ' +str(first_exp)[:10]))
        plt.plot(idx_first, func(idx_first,*popt_first),'--b')
        plt.plot(idx_second, second.IV.values,'-go',
                 label=(callput+' IV Second ' +str(second_exp)[:10]))
        plt.plot(idx_second, func(idx_second,*popt_second),'--g')
        plt.plot(idx_janleap, janleap.IV.values,'-ro',
                 label=(callput+' IV JanLeap ' +str(janleap_exp)[:10]))
        plt.plot(idx_janleap, func(idx_janleap,*popt_janleap),'--r')
        plt.grid(True)
        plt.legend(loc=0)
        plt.ylabel('Implied Volatility')
        plt.title(ticker+' Earning Date '+str(earning_date)[:10]+' exponential fitting')
        plt.show()
        
    return res
In [28]:
res = earnings_option_series('AAPL','2011-06-01','2012-06-01','C')
In [27]:
res = earnings_option_series('AAPL','2011-06-01','2012-06-01','P')

b) Do volatilities drop discontinuously across the earnings dates?

As can be seen from the above plots ATM options implied volatilities drop discontinuosly for the the front contract and in some cases for the second contract. The impact of earnings is reflected on the implied volatility of the first month as we expected.

c) Try to fit the volatility profiles running up to earnings with parabolas. Can you see any regularity? Would exponentials do better?

Looking at the above fits it does seem like the parabola fits better the implied volatilities of the front month until earnings date. Exponential do not seem to do any better.

d) What difficulty arises with following individual strikes that we avoid by following synthetic strikes?

Using the synthetic stikes for following the behaviour of implied volatilities allow us to avoid the difficulties we may find in going from one strike to another when the undelying moves. When using either ATM strike method we interpolate two different strike prices to get the synthetic option implied volatility. This is probably more reliable than looking at a single strike. Same considerations apply to the ATM delta method using a synthetic delta=0 straddle.

Exercise 3

Recall in the first lecture we discussed the possible impact of a large trade. Having a minute database allows us to examine the consequences of such a trade on a finer scale. Pick a stock from the LiveVol database and find the largest options trade to occur at least one week removed from an Earnings date and at least two times larger than any trade for the subsequent 3 days. We wish to examine minute by minute over the 3-day period.

a) Generate an implied volatility surface for your choice just prior to the large trade. If space is an issue concentrate on a surface centered about the strike and series of the large trade
b) Follow the vol surface minute by minute after the large trade
c) Can you characterize a relaxation time scale for the vol surface
d) Propose a possible trading scheme for a high frequency trader to monetize a future disturbance of this kind

In the LiveVol database we have trade tick data and minute by minute calcualations for Apple, Google and Tesla stocks options in October 2013. We looked at all stocks and analyzed the largest option trades at least one week removed from an earnings date.

In class we saw an interesting example where the whole volatility surface of the stock FDC shifted significantly downwards due to a large option trade. We were hoping that we would be able to find a similar impact from a trade in the tick database but unfortunately we were not able to. That might be due to the fact that all of the stocks we have data for are very liquid so that we would need an extremely large trade to have an obvious and lasting impact on the dynamics of the surface. Since we only have data for one month it might be the case that none of the trades for our stocks in October were large enough to have an impact on price levels or spreads. At least not an impact that is observable for more than a minute.

c)
If we had found a trade that clearly impacted the surface we could have characterized a relaxation time by observing how long it took the surface to revert back to it's "regular" state from before the trade. Since we can't observe a disturbance in the volatilty suraface we can think of two possibilities. If there is a temporary disturbance in the surface due to a large trade it either subdues in less than a minute or the trade needs to be larger relative to average trade size than any of the examples we have observed to appear in the first place.

d)
If one can reliably identify a relaxtion time for the surface of a particular stock and the relaxation time is long enough so that trades can be made before the surface reverts to it's normal state then a simple trading scheme would be take a mean-reverting position. A trader could monitor the options on the stock and whenever he sees a large trade that he expects will disturb the volatility surface he can go short (long) implied volatility on options with the same or similar strike and expiration if he expects the disturbance to be an upwards (downwards) shift. Then close the position after the surface has reverted to it's normal state. Although he would always have to be aware of other events surrounding the options such as earnings, expirations, etc.

Apple

Closest earnings date: 2013-10-28
Analysis Date Range: 2013-1-1 - 2013-10-21

In [4]:
# Get 20 largest trades for Apple
q = '''
-- Get tick data for stock
select symbol, [timestamp], expiration, strike, optiontype, 
tradesize, tradePrice, tradeConditionID, canceledTradeConditionID
into #data_trade
from XFDATA.dbo.lv_options_trades
where symbol = 'AAPL'
and timestamp < '2013-10-21'
and tradeSize > 0

-- Find largest trades
select top 20 *
from #data_trade
order by tradeSize desc

drop table #data_trade
'''

apple_trades  = query_dataframe(q)
apple_trades
Out[4]:
symbol timestamp expiration strike optiontype tradesize tradePrice tradeConditionID canceledTradeConditionID
0 AAPL 2013-10-09 12:32:04.7500000 2013-11-16 530.0 c 5000 5.40 13 0
1 AAPL 2013-10-10 14:23:27.2500000 2013-11-16 500.0 c 4500 14.30 106 0
2 AAPL 2013-10-10 15:26:42.4250000 2013-10-11 500.0 c 3021 0.11 18 0
3 AAPL 2013-10-09 12:31:29.8500000 2013-11-16 505.0 c 2500 11.10 13 0
4 AAPL 2013-10-18 12:24:54.8500000 2013-11-16 525.0 c 2350 9.25 35 1
5 AAPL 2013-10-18 14:12:56.9500000 2013-11-16 525.0 c 2350 9.35 35 0
6 AAPL 2013-10-18 14:17:43.4000000 2013-11-16 525.0 c 2350 9.25 35 2
7 AAPL 2013-10-10 14:23:17.3250000 2013-10-19 450.0 c 2275 39.10 106 0
8 AAPL 2013-10-15 10:33:15.1250000 2013-11-16 445.0 p 2160 2.42 38 0
9 AAPL 2013-10-07 10:40:16.4750000 2013-11-16 415.0 p 2100 1.31 106 0
10 AAPL 2013-10-01 10:03:52.0000000 2013-11-16 445.0 p 2050 7.50 106 0
11 AAPL 2013-10-04 10:09:18.9750000 2013-11-16 525.0 c 2000 5.35 13 0
12 AAPL 2013-10-04 10:05:50.8250000 2013-11-16 425.0 p 2000 3.10 37 0
13 AAPL 2013-10-17 10:50:18.5000000 2013-10-19 505.0 c 1800 1.85 35 0
14 AAPL 2013-10-18 14:12:56.9500000 2013-12-21 520.0 c 1692 16.49 35 0
15 AAPL 2013-10-18 14:17:34.6500000 2013-12-21 520.0 c 1692 16.35 35 2
16 AAPL 2013-10-18 12:24:54.8500000 2013-12-21 520.0 c 1692 16.35 35 1
17 AAPL 2013-10-09 11:00:46.7750000 2013-11-16 420.0 p 1500 2.68 37 0
18 AAPL 2013-10-17 10:01:31.3750000 2013-10-19 500.0 p 1500 1.70 0 1
19 AAPL 2013-10-17 10:01:35.5500000 2013-10-19 500.0 c 1500 4.50 0 1

The largest trade occured at 12:32 on October 9th and was for 5000 call option contracts with expiration 2013-11-16 and strike 530. However, it's not two times larger than any trade for the following three days since the second (4500) and third (3021) largest trades occur the next day. Looking at the three trading days following those trades we can see that they don't fulfil the criteria either. Since there are no trades that match the criteria in the problem description we will just analyze the largest trade of the month.

In [8]:
# Get volatility surface data
q = '''
select symbol, [timestamp], expiration, strike, [open], high, low, [close], 
tradeVolume, bidsize, bestbid, asksize, bestask, impliedUndPrice, activeUndPrice, iv,
datediff(day,convert(date,[timestamp]),expiration) as ExpD
from XFDATA.dbo.lv_minute_options_calcs
where symbol = 'AAPL'
and root = 'AAPL'
and timestamp between '2013-10-09 12:25' and '2013-10-09 12:40'
and optionType = 'c'
and iv > 0.05
and strike between 450 and 550
and datediff(day,convert(date,[timestamp]),expiration) < 100
order by timestamp, strike, expiration
'''

# Display minute-by-minute date around trade (K=530,T=2013-11-16)
apple_large = query_dataframe(q)
mask = (apple_large.strike == 530) & (apple_large.ExpD == 38)
apple_large.loc[mask,:]
Out[8]:
symbol timestamp expiration strike open high low close tradeVolume bidsize bestbid asksize bestask impliedUndPrice activeUndPrice iv ExpD
97 AAPL 2013-10-09 12:25:00 2013-11-16 530.0 5.44 5.44 5.44 5.44 1 108 5.35 6 5.45 478.75 478.75 0.34 38
220 AAPL 2013-10-09 12:26:00 2013-11-16 530.0 0.00 0.00 0.00 0.00 0 25 5.45 11 5.55 479.05 479.06 0.34 38
344 AAPL 2013-10-09 12:27:00 2013-11-16 530.0 5.55 5.55 5.55 5.55 1 54 5.40 55 5.55 479.25 479.25 0.34 38
467 AAPL 2013-10-09 12:28:00 2013-11-16 530.0 0.00 0.00 0.00 0.00 0 84 5.45 7 5.55 479.45 479.45 0.34 38
591 AAPL 2013-10-09 12:29:00 2013-11-16 530.0 0.00 0.00 0.00 0.00 0 34 5.40 14 5.50 479.21 479.21 0.34 38
714 AAPL 2013-10-09 12:30:00 2013-11-16 530.0 0.00 0.00 0.00 0.00 0 57 5.45 13 5.55 479.35 479.35 0.34 38
837 AAPL 2013-10-09 12:31:00 2013-11-16 530.0 0.00 0.00 0.00 0.00 0 78 5.45 7 5.55 479.35 479.35 0.34 38
960 AAPL 2013-10-09 12:32:00 2013-11-16 530.0 0.00 0.00 0.00 0.00 0 32 5.45 12 5.55 479.38 479.38 0.34 38
1083 AAPL 2013-10-09 12:33:00 2013-11-16 530.0 5.40 5.40 5.40 5.40 5000 42 5.45 17 5.55 479.38 479.38 0.34 38
1206 AAPL 2013-10-09 12:34:00 2013-11-16 530.0 0.00 0.00 0.00 0.00 0 13 5.50 22 5.60 479.38 479.38 0.34 38
1329 AAPL 2013-10-09 12:35:00 2013-11-16 530.0 0.00 0.00 0.00 0.00 0 33 5.55 12 5.65 479.64 479.66 0.34 38
1452 AAPL 2013-10-09 12:36:00 2013-11-16 530.0 5.64 5.65 5.64 5.65 10 93 5.60 48 5.75 480.03 480.03 0.34 38
1576 AAPL 2013-10-09 12:37:00 2013-11-16 530.0 0.00 0.00 0.00 0.00 0 45 5.60 29 5.70 480.11 480.11 0.34 38
1699 AAPL 2013-10-09 12:38:00 2013-11-16 530.0 0.00 0.00 0.00 0.00 0 2 5.60 7 5.65 480.11 480.11 0.34 38
1821 AAPL 2013-10-09 12:39:00 2013-11-16 530.0 5.65 5.65 5.65 5.65 6 41 5.60 6 5.70 480.00 480.00 0.34 38
1944 AAPL 2013-10-09 12:40:00 2013-11-16 530.0 0.00 0.00 0.00 0.00 0 2 5.55 29 5.65 479.95 479.95 0.34 38

Interestingly it seems like the trade had no discernable impact, at least not on it's own series (K=530,T=2013-11-16). There is almost no trading activity before or after the trade, the price is similar and the spread does not change after trade (stays at around 10 cents).
The trade price is lower than the best bid so it looks like someone is selling these contracts and since there is no observable impact we can conclude that this trade was simply not large enough to have an impact on the market. In other words there is enough liquidity in Apple to handle this transaction. However, we must keep in mind that our data is only on a minute-by-minute scale and there might have been a short term impact on the bid ask spread which was resolved in less than a minute, but we can't analyze that using the data we have access to.

We plot the volatility surface centered around the strike and expiration of our option from 12:25 to 12:40, as is expected there is no observable impact from the trade. If there was an impact we should have seen it occuring at 12:33 as shift or twist in the surface, but the surface stays in a rigid structure throughout the whole period.

In [9]:
# Plot minute-by-minute volatility surface
for idx, row in apple_large.groupby('timestamp'):
    # 2D grid construction
    spline = sp.interpolate.Rbf(row.strike,row.ExpD,row.iv,function='thin-plate')
    xi = np.linspace(min(row.strike), max(row.strike))
    yi = np.linspace(min(row.ExpD), max(row.ExpD))
    X, Y = np.meshgrid(xi, yi)
    # 3D interpolation
    Z = spline(X,Y)
    fig = plt.figure(figsize=(20, 8))
    ax = fig.gca(projection='3d')
    surf = ax.plot_surface(X, Y, Z, rstride=1, cstride=1,cmap=plt.cm.coolwarm, linewidth=0.5, antialiased=True)
    fig.colorbar(surf, shrink=0.5, aspect=5)
    ax.set_xlabel('Strike')
    ax.set_ylabel('Time-to-maturity')
    ax.set_zlabel('Implied volatility')
    ax.set_zlim([0,1])
    plt.title(idx)

Google

Closest earnings date: 2013-10-17
Analysis Date Range: 2013-1-1 - 2013-10-10 and 2013-10-24 - 2013-10-31

In [10]:
# Get 20 largest trades for Google
q = '''
-- Get tick data for stock
select symbol, [timestamp], expiration, strike, optiontype, 
tradesize, tradePrice, tradeConditionID, canceledTradeConditionID
into #data_trade
from XFDATA.dbo.lv_options_trades
where symbol = 'GOOG'
and (timestamp < '2013-10-10' or timestamp > '2013-10-24')
and tradeSize > 0

-- Find largest trades
select top 20 *
from #data_trade
order by tradeSize desc

drop table #data_trade
'''

google_trades  = query_dataframe(q)
google_trades
Out[10]:
symbol timestamp expiration strike optiontype tradesize tradePrice tradeConditionID canceledTradeConditionID
0 GOOG 2013-10-25 12:00:12.8750000 2013-10-25 1015.0 p 1482 2.94 35 0
1 GOOG 2013-10-25 12:00:12.8750000 2013-10-25 1025.0 p 1482 11.94 35 0
2 GOOG 2013-10-24 09:51:58.9250000 2013-12-21 1020.0 c 1200 43.80 7 0
3 GOOG 2013-10-30 10:50:33.4500000 2013-11-01 1065.0 c 1132 0.90 35 0
4 GOOG 2013-10-30 10:50:33.4500000 2013-11-01 1075.0 c 1132 0.65 35 0
5 GOOG 2013-10-30 09:54:45.4750000 2013-11-01 1000.0 p 989 0.33 35 0
6 GOOG 2013-10-30 09:54:45.4750000 2013-11-01 1010.0 p 989 0.63 35 0
7 GOOG 2013-10-24 10:00:55.8500000 2013-10-25 1025.0 p 862 2.94 35 0
8 GOOG 2013-10-24 10:00:55.8500000 2013-10-25 1015.0 p 862 1.52 35 0
9 GOOG 2013-10-24 09:57:18.4000000 2013-10-25 1015.0 p 859 1.55 35 0
10 GOOG 2013-10-24 09:57:18.4000000 2013-10-25 1025.0 p 859 3.30 35 0
11 GOOG 2013-10-30 11:01:42.5000000 2013-11-01 1025.0 p 519 2.80 95 0
12 GOOG 2013-10-24 09:51:38.3500000 2013-11-16 1065.0 c 500 12.00 7 0
13 GOOG 2013-10-24 09:52:14.5000000 2013-12-21 1090.0 c 500 16.10 7 0
14 GOOG 2013-10-24 09:52:30.3250000 2013-12-21 1080.0 c 500 18.90 7 0
15 GOOG 2013-10-28 13:15:36.8250000 2014-01-18 1000.0 c 500 51.20 13 0
16 GOOG 2013-10-25 14:46:17.2750000 2013-11-16 1040.0 c 500 9.50 35 0
17 GOOG 2013-10-25 14:46:17.2750000 2013-11-16 1035.0 c 500 11.10 35 0
18 GOOG 2013-10-25 14:46:17.2750000 2013-11-16 1065.0 c 500 4.40 35 0
19 GOOG 2013-10-25 14:46:26.4500000 2013-12-21 1080.0 c 500 9.90 35 0

The largest trade of October is shared between two trades and it looks like they are a part of a spread. The trade occured at 12:00 on October 25th and was for 1482 put option contracts expiring at the end of that day with strikes 1015 and 1025. Since the trade occured at the expiration date of the options we can reasonably assume that in this case someone is closing out a spread position and it's unlikely that the trade had an impact on the market. No other smaller trades in the month fulfil the criteria we are looking for so we will go forward and analyze the activity around this trade.

In [23]:
# Get volatility surface data
q = '''
select symbol, [timestamp], expiration, strike, [open], high, low, [close], 
tradeVolume, bidsize, bestbid, asksize, bestask, impliedUndPrice, activeUndPrice, iv,
datediff(day,convert(date,[timestamp]),expiration) as ExpD
from XFDATA.dbo.lv_minute_options_calcs
where symbol = 'GOOG'
and root = 'GOOG'
and timestamp between '2013-10-25 11:50' and '2013-10-25 12:10'
and optionType = 'p'
and strike between 900 and 1100
and datediff(day,convert(date,[timestamp]),expiration) < 100
and iv > 0.05
order by timestamp, strike, expiration
'''
# Display minute-by-minute date around trade (K=1025,T=2013-10-25)
google_large  = query_dataframe(q)
mask = (google_large.strike == 1015) & (google_large.ExpD == 0)
mask2 = (google_large.strike == 1025) & (google_large.ExpD == 0)
google_large.loc[mask,:].append(google_large.loc[mask2,:])
Out[23]:
symbol timestamp expiration strike open high low close tradeVolume bidsize bestbid asksize bestask impliedUndPrice activeUndPrice iv ExpD
184 GOOG 2013-10-25 11:50:00 2013-10-25 1015.0 1.97 2.05 1.97 2.00 23 1 2.00 3 2.20 1015.47 1015.47 0.17 0
494 GOOG 2013-10-25 11:51:00 2013-10-25 1015.0 2.12 2.71 2.12 2.53 246 7 2.25 9 2.40 1014.77 1014.77 0.17 0
807 GOOG 2013-10-25 11:52:00 2013-10-25 1015.0 2.25 2.50 1.82 2.50 118 13 2.35 10 2.50 1014.57 1014.57 0.17 0
1119 GOOG 2013-10-25 11:53:00 2013-10-25 1015.0 2.35 2.45 2.14 2.20 22 14 2.10 10 2.35 1015.00 1015.00 0.17 0
1441 GOOG 2013-10-25 11:54:00 2013-10-25 1015.0 2.20 2.39 2.10 2.27 32 1 2.05 1 2.30 1015.00 1015.00 0.16 0
1751 GOOG 2013-10-25 11:55:00 2013-10-25 1015.0 2.35 2.45 2.35 2.45 11 4 2.25 1 2.55 1014.77 1014.77 0.18 0
2062 GOOG 2013-10-25 11:56:00 2013-10-25 1015.0 2.44 2.50 2.34 2.50 44 1 2.40 20 2.65 1014.60 1014.60 0.18 0
2373 GOOG 2013-10-25 11:57:00 2013-10-25 1015.0 2.30 2.50 2.25 2.50 36 2 2.15 1 2.40 1014.97 1015.00 0.18 0
2696 GOOG 2013-10-25 11:58:00 2013-10-25 1015.0 2.25 2.45 1.87 2.16 403 1 2.30 1 2.70 1014.55 1014.55 0.17 0
3008 GOOG 2013-10-25 11:59:00 2013-10-25 1015.0 2.11 4.00 2.07 3.30 219 1 3.30 1 3.70 1012.70 1012.73 0.17 0
3323 GOOG 2013-10-25 12:00:00 2013-10-25 1015.0 3.30 3.70 3.30 3.34 50 13 3.00 1 3.40 1013.12 1013.12 0.17 0
3640 GOOG 2013-10-25 12:01:00 2013-10-25 1015.0 3.30 3.30 2.70 2.80 1528 1 2.75 4 3.00 1013.75 1013.75 0.17 0
3958 GOOG 2013-10-25 12:02:00 2013-10-25 1015.0 2.75 2.75 2.70 2.70 7 3 2.60 4 2.85 1014.10 1014.07 0.18 0
4282 GOOG 2013-10-25 12:03:00 2013-10-25 1015.0 2.60 2.70 2.53 2.70 53 2 2.55 2 2.85 1013.97 1013.97 0.17 0
4601 GOOG 2013-10-25 12:04:00 2013-10-25 1015.0 3.00 3.00 2.78 2.78 6 3 2.65 3 3.00 1013.85 1013.85 0.18 0
4918 GOOG 2013-10-25 12:05:00 2013-10-25 1015.0 3.00 3.30 3.00 3.20 20 3 3.40 16 3.80 1012.75 1012.75 0.18 0
5226 GOOG 2013-10-25 12:06:00 2013-10-25 1015.0 3.70 4.70 3.70 4.70 16 3 4.50 6 5.00 1011.45 1011.45 0.17 0
5537 GOOG 2013-10-25 12:07:00 2013-10-25 1015.0 4.45 4.45 3.70 3.81 45 7 3.80 5 4.00 1012.40 1012.40 0.19 0
5857 GOOG 2013-10-25 12:08:00 2013-10-25 1015.0 3.85 4.20 3.77 4.05 37 1 3.80 2 4.20 1012.22 1012.22 0.19 0
6175 GOOG 2013-10-25 12:09:00 2013-10-25 1015.0 4.00 4.00 3.60 3.60 32 25 3.20 10 3.80 1012.85 1012.85 0.18 0
6494 GOOG 2013-10-25 12:10:00 2013-10-25 1015.0 3.70 3.70 3.70 3.70 1 34 3.40 1 3.90 1012.77 1012.77 0.18 0
200 GOOG 2013-10-25 11:50:00 2013-10-25 1025.0 9.74 9.74 9.74 9.74 10 1 9.60 2 9.80 1015.47 1015.47 0.13 0
510 GOOG 2013-10-25 11:51:00 2013-10-25 1025.0 9.80 11.40 9.80 11.40 45 3 10.10 1 10.70 1014.77 1014.77 0.20 0
823 GOOG 2013-10-25 11:52:00 2013-10-25 1025.0 0.00 0.00 0.00 0.00 0 5 10.40 4 11.00 1014.57 1014.57 0.23 0
1135 GOOG 2013-10-25 11:53:00 2013-10-25 1025.0 0.00 0.00 0.00 0.00 0 1 10.00 3 10.60 1015.00 1015.00 0.22 0
1457 GOOG 2013-10-25 11:54:00 2013-10-25 1025.0 0.00 0.00 0.00 0.00 0 3 9.90 1 10.60 1015.00 1015.00 0.18 0
1767 GOOG 2013-10-25 11:55:00 2013-10-25 1025.0 10.50 10.50 10.50 10.50 1 2 10.20 1 10.90 1014.77 1014.77 0.22 0
2078 GOOG 2013-10-25 11:56:00 2013-10-25 1025.0 10.40 10.54 10.40 10.54 11 1 10.40 1 10.90 1014.60 1014.60 0.21 0
2389 GOOG 2013-10-25 11:57:00 2013-10-25 1025.0 10.77 10.77 10.77 10.77 10 2 10.00 1 10.60 1014.97 1015.00 0.23 0
2712 GOOG 2013-10-25 11:58:00 2013-10-25 1025.0 10.90 11.10 10.00 10.86 435 2 10.30 11 11.00 1014.55 1014.55 0.18 0
3024 GOOG 2013-10-25 11:59:00 2013-10-25 1025.0 10.81 12.70 10.77 12.45 206 1 12.10 1 12.80 1012.70 1012.73 0.24 0
3339 GOOG 2013-10-25 12:00:00 2013-10-25 1025.0 12.60 12.60 12.49 12.49 25 12 11.70 1 12.40 1013.12 1013.12 0.22 0
3656 GOOG 2013-10-25 12:01:00 2013-10-25 1025.0 12.04 12.04 11.50 11.70 1487 27 11.20 2 11.90 1013.75 1013.75 0.22 0
3974 GOOG 2013-10-25 12:02:00 2013-10-25 1025.0 0.00 0.00 0.00 0.00 0 1 11.00 2 11.60 1014.10 1014.07 0.26 0
4298 GOOG 2013-10-25 12:03:00 2013-10-25 1025.0 0.00 0.00 0.00 0.00 0 2 10.90 22 11.60 1013.97 1013.97 0.23 0
4617 GOOG 2013-10-25 12:04:00 2013-10-25 1025.0 11.13 11.33 11.13 11.33 2 1 11.10 2 11.70 1013.85 1013.85 0.24 0
4934 GOOG 2013-10-25 12:05:00 2013-10-25 1025.0 0.00 0.00 0.00 0.00 0 1 12.20 4 12.80 1012.75 1012.75 0.26 0
5242 GOOG 2013-10-25 12:06:00 2013-10-25 1025.0 13.50 13.60 13.50 13.60 13 1 13.80 1 14.40 1011.45 1011.45 0.24 0
5553 GOOG 2013-10-25 12:07:00 2013-10-25 1025.0 0.00 0.00 0.00 0.00 0 2 12.60 1 13.20 1012.40 1012.40 0.27 0
5873 GOOG 2013-10-25 12:08:00 2013-10-25 1025.0 12.93 12.93 12.93 12.93 2 1 12.70 101 13.50 1012.22 1012.22 0.26 0
6191 GOOG 2013-10-25 12:09:00 2013-10-25 1025.0 12.50 12.60 11.91 12.60 6 7 11.90 4 12.90 1012.85 1012.85 0.26 0
6510 GOOG 2013-10-25 12:10:00 2013-10-25 1025.0 0.00 0.00 0.00 0.00 0 21 12.10 125 13.10 1012.77 1012.77 0.27 0

The table above show the price and volatility data for both options around the trade (K=1015,T=2013-11-25) and (K=1025,T=2013-11-25). It looks like this trade is a part of a larger trade that is spread out into parts from 11:58 to 12:01 for around 2000 contracts. We can't see the whole order book for the options but since the bid and ask sizes are so low and it's so close to expiration we can assume that the option is not very liquid. Therefore, it is not surprising to see these big trades temporarly affect the option prices (bid/ask spread) since the trades are likely clearing up a big portion of the order book. After the trades we can observe that the option prices are gradually climbing but that is probably due to the fact that the stock price is dropping and since the option is really close to expiration it's price is very sensitive to all underlying price changes. At least it is hard to attribute it to this trade with any confidence and there is no clear disturbance followed by a relaxation. It is more informative to look at the big picture, the whole volatility surface, to determine if the trade had an effect on the dynamics of the stock options that day.

We plot the volatility surface centered around the strike and expiration of our option from 10:50 to 12:10, there does seem to be some action in the shorter maturity 1000 to 1100 strikes but after closer inspection these changes are simply due to missing data. Calculations are missing from the database on all timestamps where the surface is flat on those regions. Example: All data available at 11:53 but K=[1000,1100] has missing data on 11:54. Taking this into account we can't spot any impact obvious impacts on the dynamics of the surface from the trades. The minute-by-minute dynamics are similar to those we observed when looking at it in the three days leading up to the trade.

In [24]:
# Plot minute-by-minute volatility surface
for idx, row in google_large.groupby('timestamp'):
    # 2D grid construction
    spline = sp.interpolate.Rbf(row.strike,row.ExpD,row.iv,function='thin-plate')
    xi = np.linspace(min(row.strike), max(row.strike))
    yi = np.linspace(min(row.ExpD), max(row.ExpD))
    X, Y = np.meshgrid(xi, yi)
    # 3D interpolation
    Z = spline(X,Y)
    fig = plt.figure(figsize=(15, 8))
    ax = fig.gca(projection='3d')
    surf = ax.plot_surface(X, Y, Z, rstride=1, cstride=1,cmap=plt.cm.coolwarm, linewidth=0.5, antialiased=True)
    fig.colorbar(surf, shrink=0.5, aspect=5)
    ax.set_xlabel('Strike')
    ax.set_ylabel('Time-to-maturity')
    ax.set_zlabel('Implied volatility')
    ax.set_zlim([0,2])
    plt.title(idx)
C:\Users\Helgi\Anaconda3\lib\site-packages\matplotlib\pyplot.py:516: RuntimeWarning: More than 20 figures have been opened. Figures created through the pyplot interface (`matplotlib.pyplot.figure`) are retained until explicitly closed and may consume too much memory. (To control this warning, see the rcParam `figure.max_open_warning`).
  max_open_warning, RuntimeWarning)

Tesla

Closest earnings date: 2013-11-5
Analysis Date Range: 2013-1-1 - 2013-10-29

In [16]:
# Get 20 largest trades for Tesla
q = '''
-- Get tick data for stock
select symbol, [timestamp], expiration, strike, optiontype, 
tradesize, tradePrice, tradeConditionID, canceledTradeConditionID
into #data_trade
from XFDATA.dbo.lv_options_trades
where symbol = 'TSLA'
and timestamp < '2013-10-29'
and tradeSize > 0

-- Find largest trades
select top 20 *
from #data_trade
order by tradeSize desc

drop table #data_trade
'''

tesla_trades  = query_dataframe(q)
tesla_trades
Out[16]:
symbol timestamp expiration strike optiontype tradesize tradePrice tradeConditionID canceledTradeConditionID
0 TSLA 2013-10-10 10:25:41.6500000 2013-10-11 185.0 c 2237 0.21 35 0
1 TSLA 2013-10-10 10:25:41.6500000 2013-10-11 180.0 c 2237 0.41 35 0
2 TSLA 2013-10-24 15:42:31.7000000 2015-01-17 170.0 c 2000 44.00 7 0
3 TSLA 2013-10-24 15:42:48.3750000 2015-01-17 190.0 c 2000 36.90 13 0
4 TSLA 2013-10-01 12:42:52.1000000 2015-01-17 180.0 c 1500 51.60 35 0
5 TSLA 2013-10-01 12:43:02.1500000 2015-01-17 210.0 c 1500 39.80 35 0
6 TSLA 2013-10-02 15:59:18.6000000 2013-10-04 195.0 c 1274 0.50 95 0
7 TSLA 2013-10-25 09:30:32.7000000 2013-11-16 340.0 c 1082 0.01 18 0
8 TSLA 2013-10-01 15:58:20.6000000 2014-01-18 180.0 p 1000 18.50 37 0
9 TSLA 2013-10-02 14:59:29.1750000 2013-10-04 205.0 c 1000 0.13 0 0
10 TSLA 2013-10-03 10:39:44.7500000 2013-10-04 150.0 p 952 0.34 35 0
11 TSLA 2013-10-03 10:39:44.7500000 2013-10-04 155.0 p 952 0.46 35 0
12 TSLA 2013-10-11 15:43:18.7500000 2013-10-19 185.0 p 941 8.93 35 0
13 TSLA 2013-10-11 15:43:18.7500000 2013-10-11 185.0 p 941 6.73 35 0
14 TSLA 2013-10-03 10:32:36.2000000 2013-10-04 185.0 c 902 0.30 35 0
15 TSLA 2013-10-03 10:32:36.2000000 2013-10-04 190.0 c 902 0.18 35 0
16 TSLA 2013-10-03 10:25:43.9000000 2015-01-17 50.0 p 900 2.85 106 0
17 TSLA 2013-10-23 14:05:20.8250000 2013-11-16 190.0 c 900 5.30 35 0
18 TSLA 2013-10-23 14:05:20.8250000 2013-11-16 190.0 c 900 5.30 35 0
19 TSLA 2013-10-23 14:05:20.9750000 2013-11-16 175.0 c 900 9.50 35 0

It looks like we have the same case as for Google for the largest trade, it is shared between two trades and it looks like it is someone closing out a spread trade. It occured on October 10th at 10:25 and was for 2237 call option contracts at strikes 180 and 185. In addition, it seems like the 6 largest trades in October are all spread trades. We don't expect these spread trades to have a noticable impact on the market or surface dynamics but since no other smaller trades in the month fulfil the criteria we are looking for so we will go forward and analyze the activity around this trade.

In [27]:
# Get volatility surface data
q = '''
select symbol, [timestamp], expiration, strike, [open], high, low, [close], 
tradeVolume, bidsize, bestbid, asksize, bestask, impliedUndPrice, activeUndPrice, iv,
datediff(day,convert(date,[timestamp]),expiration) as ExpD
from XFDATA.dbo.lv_minute_options_calcs
where symbol = 'TSLA'
and timestamp between '2013-10-10 10:20' and '2013-10-10 10:35'
and optionType = 'c'
and strike between 150 and 250
and datediff(day,convert(date,[timestamp]),expiration) < 100
and iv > 0.05
order by timestamp, strike, expiration
'''
# Display minute-by-minute date around trade (K=185,T=2013-10-11)
tesla_large  = query_dataframe(q)
tesla_large.head()
mask = (tesla_large.strike == 180) & (tesla_large.ExpD == 1)
mask2 = (tesla_large.strike == 185) & (tesla_large.ExpD == 1)
tesla_large.loc[mask,:].append(tesla_large.loc[mask2,:])
Out[27]:
symbol timestamp expiration strike open high low close tradeVolume bidsize bestbid asksize bestask impliedUndPrice activeUndPrice iv ExpD
39 TSLA 2013-10-10 10:20:00 2013-10-11 180.0 0.00 0.00 0.00 0.00 0 10 0.37 15 0.40 171.08 171.08 0.65 1
170 TSLA 2013-10-10 10:21:00 2013-10-11 180.0 0.35 0.37 0.35 0.36 8 33 0.33 37 0.40 170.90 170.93 0.65 1
301 TSLA 2013-10-10 10:22:00 2013-10-11 180.0 0.35 0.35 0.35 0.35 3 47 0.33 37 0.40 170.96 170.96 0.64 1
431 TSLA 2013-10-10 10:23:00 2013-10-11 180.0 0.33 0.39 0.33 0.39 8 1 0.32 41 0.40 170.90 170.93 0.65 1
561 TSLA 2013-10-10 10:24:00 2013-10-11 180.0 0.40 0.40 0.40 0.40 1 52 0.37 15 0.40 171.36 171.36 0.62 1
692 TSLA 2013-10-10 10:25:00 2013-10-11 180.0 0.40 0.46 0.38 0.46 149 1 0.40 110 0.48 171.56 171.56 0.65 1
823 TSLA 2013-10-10 10:26:00 2013-10-11 180.0 0.38 0.46 0.38 0.41 2371 1 0.39 67 0.46 171.62 171.62 0.64 1
953 TSLA 2013-10-10 10:27:00 2013-10-11 180.0 0.45 0.47 0.45 0.47 6 1 0.45 1 0.49 171.99 171.99 0.64 1
1083 TSLA 2013-10-10 10:28:00 2013-10-11 180.0 0.45 0.45 0.45 0.45 5 60 0.41 101 0.49 172.10 172.10 0.62 1
1213 TSLA 2013-10-10 10:29:00 2013-10-11 180.0 0.49 0.50 0.48 0.50 107 2 0.46 157 0.56 172.48 172.51 0.63 1
1343 TSLA 2013-10-10 10:30:00 2013-10-11 180.0 0.00 0.00 0.00 0.00 0 2 0.42 63 0.56 172.52 172.52 0.61 1
1474 TSLA 2013-10-10 10:31:00 2013-10-11 180.0 0.54 0.54 0.53 0.53 52 1 0.47 155 0.56 172.61 172.61 0.62 1
1604 TSLA 2013-10-10 10:32:00 2013-10-11 180.0 0.51 0.52 0.51 0.52 2 25 0.50 95 0.55 172.55 172.54 0.63 1
1735 TSLA 2013-10-10 10:33:00 2013-10-11 180.0 0.50 0.50 0.45 0.45 56 32 0.42 196 0.49 172.23 172.20 0.62 1
1866 TSLA 2013-10-10 10:34:00 2013-10-11 180.0 0.00 0.00 0.00 0.00 0 1 0.40 21 0.45 172.15 172.15 0.61 1
1996 TSLA 2013-10-10 10:35:00 2013-10-11 180.0 0.42 0.42 0.34 0.35 21 38 0.34 38 0.43 171.93 171.93 0.60 1
46 TSLA 2013-10-10 10:20:00 2013-10-11 185.0 0.13 0.13 0.12 0.12 30 47 0.12 30 0.17 171.08 171.08 0.71 1
177 TSLA 2013-10-10 10:21:00 2013-10-11 185.0 0.00 0.00 0.00 0.00 0 20 0.12 51 0.17 170.90 170.93 0.72 1
308 TSLA 2013-10-10 10:22:00 2013-10-11 185.0 0.12 0.12 0.12 0.12 20 10 0.12 54 0.17 170.96 170.96 0.72 1
438 TSLA 2013-10-10 10:23:00 2013-10-11 185.0 0.00 0.00 0.00 0.00 0 33 0.12 30 0.16 170.90 170.93 0.72 1
568 TSLA 2013-10-10 10:24:00 2013-10-11 185.0 0.00 0.00 0.00 0.00 0 33 0.12 23 0.16 171.36 171.36 0.70 1
699 TSLA 2013-10-10 10:25:00 2013-10-11 185.0 0.15 0.18 0.15 0.16 143 43 0.12 12 0.16 171.56 171.56 0.69 1
830 TSLA 2013-10-10 10:26:00 2013-10-11 185.0 0.18 0.21 0.16 0.21 2382 109 0.12 6 0.18 171.62 171.62 0.70 1
960 TSLA 2013-10-10 10:27:00 2013-10-11 185.0 0.00 0.00 0.00 0.00 0 91 0.13 6 0.18 171.99 171.99 0.69 1
1090 TSLA 2013-10-10 10:28:00 2013-10-11 185.0 0.00 0.00 0.00 0.00 0 155 0.13 6 0.18 172.10 172.10 0.68 1
1220 TSLA 2013-10-10 10:29:00 2013-10-11 185.0 0.18 0.18 0.18 0.18 6 113 0.12 49 0.21 172.48 172.51 0.67 1
1350 TSLA 2013-10-10 10:30:00 2013-10-11 185.0 0.00 0.00 0.00 0.00 0 81 0.12 40 0.21 172.52 172.52 0.67 1
1481 TSLA 2013-10-10 10:31:00 2013-10-11 185.0 0.15 0.15 0.15 0.15 18 65 0.13 51 0.21 172.61 172.61 0.67 1
1611 TSLA 2013-10-10 10:32:00 2013-10-11 185.0 0.00 0.00 0.00 0.00 0 86 0.13 78 0.21 172.55 172.54 0.67 1
1742 TSLA 2013-10-10 10:33:00 2013-10-11 185.0 0.13 0.15 0.13 0.14 147 43 0.12 62 0.17 172.23 172.20 0.67 1
1873 TSLA 2013-10-10 10:34:00 2013-10-11 185.0 0.00 0.00 0.00 0.00 0 11 0.12 5 0.15 172.15 172.15 0.66 1
2003 TSLA 2013-10-10 10:35:00 2013-10-11 185.0 0.00 0.00 0.00 0.00 0 10 0.12 193 0.15 171.93 171.93 0.67 1

The table above show the price and volatility data for both options around the trade (K=180,T=2013-10-11) and (K=1025,T=2013-10-11). The analysis for Google pretty much applies here as well, we have a spread of options close to expiry and large trades. The trades do seem to affect the option bid/ask spreads although less here than in the Google case. But there is no clear period of disturbance followed by a relaxation.

We plot the volatility surface centered around the strike and expiration of our option from 10:25 to 10:40. Yet again the volatility surface shows no disturbance around the trade (10:26).

In [21]:
# Plot minute-by-minute volatility surface
for idx, row in tesla_large.groupby('timestamp'):
    # 2D grid construction
    spline = sp.interpolate.Rbf(row.strike,row.ExpD,row.iv,function='thin-plate')
    xi = np.linspace(min(row.strike), max(row.strike))
    yi = np.linspace(min(row.ExpD), max(row.ExpD))
    X, Y = np.meshgrid(xi, yi)
    # 3D interpolation
    Z = spline(X,Y)
    fig = plt.figure(figsize=(15, 8))
    ax = fig.gca(projection='3d')
    surf = ax.plot_surface(X, Y, Z, rstride=1, cstride=1,cmap=plt.cm.coolwarm, linewidth=0.5, antialiased=True)
    fig.colorbar(surf, shrink=0.5, aspect=5)
    ax.set_xlabel('Strike')
    ax.set_ylabel('Time-to-maturity')
    ax.set_zlabel('Implied volatility')
    ax.set_zlim([0,2])
    plt.title(idx)